﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QueryMessageBuffer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[QueryMessageBuffer]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Transformation_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Transformation_Select]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Transformation_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Transformation_InsertUpdate]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Transformation_DeleteByClientDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Transformation_DeleteByClientDbId]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Transformation_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Transformation_Select]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Transformation_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Transformation_InsertUpdate]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Advertisement_Transformation_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Advertisement_Transformation_Select]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Advertisement_Transformation_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Advertisement_Transformation_InsertUpdate]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MessageBuffer_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MessageBuffer_InsertUpdate]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MessageBuffer_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Select]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Select]
    @SubscriberDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
			Persistent,
            Paused,
            ExpiryDateTimeUtc,
            MessageValidityDurationSecs,
			DeliveryAttemptIntervalSecs,
			MaxDeliveryAttemptCount,
			RequestPipelineId,
			ResponsePipelineId
        FROM Subscription
        WHERE
            SubscriberDbId = @SubscriberDbId AND
            (
                ExpiryDateTimeUtc IS NULL OR
                ExpiryDateTimeUtc > SYSUTCDATETIME()
            )
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Advertisement_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Advertisement_Select]
GO
CREATE PROCEDURE [dbo].[sp_Advertisement_Select]
    @PublisherDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            Paused,
            ExpiryDateTimeUtc,
            GenerateDefaultResponse,
			RequestPipelineId,
			ResponsePipelineId
        FROM Advertisement
        WHERE
            PublisherDbId = @PublisherDbId AND
            (
                ExpiryDateTimeUtc IS NULL OR
                ExpiryDateTimeUtc > SYSUTCDATETIME()
            )
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_InsertUpdate]
    @Id nvarchar(250),
	@Persistent bit,
    @SubscriberDbId bigint,
    @Paused bit,
    @ExpiryDateTimeUtc datetime2 = null,
    @MessageValidityDurationSecs bigint = null,
	@DeliveryAttemptIntervalSecs bigint = null,
	@MaxDeliveryAttemptCount int = null,
	@RequestPipelineId nvarchar(250) = null,
	@ResponsePipelineId nvarchar(250) = null,
    @SubscriptionDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Subscription
        SET
		    Persistent = @Persistent,
			DeliveryAttemptIntervalSecs = @DeliveryAttemptIntervalSecs,
			MaxDeliveryAttemptCount = @MaxDeliveryAttemptCount,
            Paused = @Paused,
            ExpiryDateTimeUtc = @ExpiryDateTimeUtc,
            MessageValidityDurationSecs = @MessageValidityDurationSecs,
            RequestPipelineId = @RequestPipelineId,
            ResponsePipelineId = @ResponsePipelineId
        WHERE
            Id = @Id AND
            SubscriberDbId = @SubscriberDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @SubscriptionDbId =
            (
                SELECT DbId
                FROM Subscription
                WHERE
                    Id = @Id AND
                    SubscriberDbId = @SubscriberDbId
            )

            DELETE FROM Subscription_Filter
            WHERE
                SubscriptionDbId = @SubscriptionDbId
            
            DELETE FROM Subscription_Endpoint
            WHERE
                SubscriptionDbId = @SubscriptionDbId

        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Subscription
            (
                Id,
				Persistent,
				DeliveryAttemptIntervalSecs,
				MaxDeliveryAttemptCount,
                SubscriberDbId,
                Paused,
                ExpiryDateTimeUtc,
                MessageValidityDurationSecs,
                RequestPipelineId,
                ResponsePipelineId
            )
            VALUES
            (
                @Id,
				@Persistent,
				@DeliveryAttemptIntervalSecs,
				@MaxDeliveryAttemptCount,
                @SubscriberDbId,
                @Paused,
                @ExpiryDateTimeUtc,
                @MessageValidityDurationSecs,
                @RequestPipelineId,
                @ResponsePipelineId
            )
            SET @SubscriptionDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_DeleteBySubscriberDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_DeleteBySubscriberDbId]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_DeleteBySubscriberDbId]
    @SubscriberDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM Subscription_Filter
        WHERE
            SubscriptionDbId IN
                (SELECT SubscriptionDbId FROM Subscription WHERE SubscriberDbId = @SubscriberDbId)
        
        DELETE FROM Subscription_Endpoint
        WHERE
            SubscriptionDbId IN
                (SELECT SubscriptionDbId FROM Subscription WHERE SubscriberDbId = @SubscriberDbId)

        DELETE FROM Subscription
        WHERE
            SubscriberDbId = @SubscriberDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Delete]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Delete]
    @ClientId nvarchar(250),
    @SubscriptionId nvarchar(250)
AS
BEGIN
    BEGIN TRY
        DECLARE @ClientDbId bigint
        SET @ClientDbId = (SELECT DbId FROM Client WHERE Id = @ClientId)

        DECLARE @SubscriptionDbId bigint
        SET @SubscriptionDbId = (SELECT DbId FROM Subscription WHERE Id = @SubscriptionId AND SubscriberDbId = @ClientDbId)
        
        DELETE FROM Subscription_Filter
        WHERE
            SubscriptionDbId = @SubscriptionDbId

        DELETE FROM Subscription_Endpoint
        WHERE
            SubscriptionDbId = @SubscriptionDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Advertisement_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Advertisement_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Advertisement_InsertUpdate]
    @Id nvarchar(250),
    @PublisherDbId bigint,
    @Paused bit,
    @ExpiryDateTimeUtc datetime2 = null,
    @GenerateDefaultResponse bit = false,
    @RequestPipelineId nvarchar(250) = null,
    @ResponsePipelineId nvarchar(250) = null,
    @AdvertisementDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Advertisement
        SET
            Paused = @Paused,
            ExpiryDateTimeUtc = @ExpiryDateTimeUtc,
            GenerateDefaultResponse = @GenerateDefaultResponse,
            RequestPipelineId = @RequestPipelineId,
            ResponsePipelineId = @ResponsePipelineId
        WHERE
            Id = @Id AND
            PublisherDbId = @PublisherDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @AdvertisementDbId =
            (
                SELECT DbId
                FROM Advertisement
                WHERE
                    Id = @Id AND
                    PublisherDbId = @PublisherDbId
            )
            
            DELETE FROM Advertisement_Filter
            WHERE
                AdvertisementDbId = @AdvertisementDbId
            
            DELETE FROM Advertisement_Topic
            WHERE
                AdvertisementDbId = @AdvertisementDbId
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Advertisement
            (
                Id,
                PublisherDbId,
                Paused,
                ExpiryDateTimeUtc,
                GenerateDefaultResponse,
                RequestPipelineId,
                ResponsePipelineId
            )
            VALUES
            (
                @Id,
                @PublisherDbId,
                @Paused,
                @ExpiryDateTimeUtc,
                @GenerateDefaultResponse,
                @RequestPipelineId,
                @ResponsePipelineId
            )
            SET @AdvertisementDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Advertisement_DeleteByPublisherDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Advertisement_DeleteByPublisherDbId]
GO
CREATE PROCEDURE [dbo].[sp_Advertisement_DeleteByPublisherDbId]
    @PublisherDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM Advertisement_Filter
        WHERE
            AdvertisementDbId IN
                (SELECT AdvertisementDbId FROM Advertisement WHERE PublisherDbId = @PublisherDbId)

        DELETE FROM Advertisement_Topic
        WHERE
            AdvertisementDbId IN
                (SELECT AdvertisementDbId FROM Advertisement WHERE PublisherDbId = @PublisherDbId)
                
        DELETE FROM Advertisement_Response
        WHERE
            AdvertisementDbId IN
                (SELECT AdvertisementDbId FROM Advertisement WHERE PublisherDbId = @PublisherDbId)
                
        DELETE FROM Advertisement
        WHERE
            PublisherDbId = @PublisherDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Advertisement_DeleteByPublisherDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Advertisement_DeleteByPublisherDbId]
GO
CREATE PROCEDURE [dbo].[sp_Advertisement_Delete]
    @ClientId nvarchar(250),
    @AdvertisementId nvarchar(250)
AS
BEGIN
    BEGIN TRY
        DECLARE @ClientDbId bigint
        SET @ClientDbId = (SELECT DbId FROM Client WHERE Id = @ClientId)

        DECLARE @AdvertisementDbId bigint
        SET @AdvertisementDbId = (SELECT DbId FROM Advertisement WHERE Id = @AdvertisementId AND PublisherDbId = @ClientDbId)
        
        DELETE FROM Advertisement_Filter
        WHERE
            AdvertisementDbId = @AdvertisementDbId
        
        DELETE FROM Advertisement_Topic
        WHERE
            AdvertisementDbId = @AdvertisementDbId
        
        DELETE FROM Advertisement_Response
        WHERE
            AdvertisementDbId = @AdvertisementDbId

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Client_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Client_Delete]
GO
CREATE PROCEDURE [dbo].[sp_Client_Delete]
    @ClientId nvarchar(250)
AS
BEGIN
    BEGIN TRY
        DECLARE @ClientDbId bigint
        SET @ClientDbId = (SELECT DbId FROM Client WHERE Id = @ClientId)
        
        EXEC sp_Subscription_DeleteBySubscriberDbId @ClientDbId
        
        EXEC sp_Advertisement_DeleteByPublisherDbId @ClientDbId
        DELETE FROM Publisher_Topic WHERE PublisherDbId = @ClientDbId
        
        EXEC sp_NamespaceDefinition_DeleteByClientDbId @ClientDbId

        EXEC sp_Filter_DeleteByClientDbId @ClientDbId

		EXEC sp_Pipeline_DeleteByClientDbId @ClientDbId

        EXEC sp_Endpoint_DeleteByClientDbId @ClientDbId
        
        EXEC sp_Response_DeleteByPublisherDbId @ClientDbId
        
        DELETE FROM Publisher WHERE PublisherDbId = @ClientDbId
        DELETE FROM Subscriber WHERE SubscriberDbId = @ClientDbId
        DELETE FROM Client WHERE DbId = @ClientDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Pipeline_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Pipeline_Select]
GO
CREATE PROCEDURE [dbo].[sp_Pipeline_Select]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            PipelineInfo
        FROM Pipeline p
        WHERE
            p.ClientDbId = @ClientDbId
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Pipeline_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Pipeline_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Pipeline_InsertUpdate]
    @Id nvarchar(250),
    @ClientDbId bigint,
    @PipelineInfo varbinary(max),
    @PipelineDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Pipeline
        SET
			PipelineInfo = @PipelineInfo
        WHERE
            Id = @Id AND
            ClientDbId = @ClientDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @PipelineDbId =
            (
                SELECT DbId
                FROM Pipeline
                WHERE
                    Id = @Id AND
                    ClientDbId = @ClientDbId
            )
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Pipeline
            (
                Id,
                ClientDbId,
                PipelineInfo
            )
            VALUES
            (
                @Id,
                @ClientDbId,
                @PipelineInfo
            )
            SET @PipelineDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Pipeline_DeleteByClientDbId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Pipeline_DeleteByClientDbId]
GO
CREATE PROCEDURE [dbo].[sp_Pipeline_DeleteByClientDbId]
    @ClientDbId bigint
AS
BEGIN
    BEGIN TRY
        DELETE FROM Pipeline
        WHERE
            ClientDbId = @ClientDbId
            
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO